﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using QuanLyThuVien.Presentation;

namespace QuanLyThuVien.DataAccess
{
    class clsDatabase
    {
        SqlConnection sqlcon;
        SqlCommand sqlcom;
        SqlDataAdapter sqlda;
        SqlDataReader sqldr;
        DataSet ds;
        DataTable dt;
        string strcon = @"Data Source=.\SQLEXPRESS;Initial Catalog=QLTV;Integrated Security=True";

        void MoKeNoi()
        {
            sqlcon = new SqlConnection(strcon);
            if (sqlcon.State == ConnectionState.Closed)
                sqlcon.Open();
        }

        void DongKetNoi()
        {
            sqlcon.Close();
        }

        public void ThuThiSQL(string SQL)
        {
            MoKeNoi();
            sqlcom = new SqlCommand(SQL, sqlcon);
            sqlcom.ExecuteNonQuery();
            DongKetNoi();
        }

        public void LoadLenCombobox(ComboBox cb, string SQL, int chiso)
        {
            cb.Items.Clear();
            MoKeNoi();
            sqlcom = new SqlCommand(SQL, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                cb.Items.Add(sqldr[chiso].ToString());
            }
            DongKetNoi();
        }

        public void LoadLenDG(DataGridView dg, string SQL)
        {
            ds = new DataSet();
            sqlda = new SqlDataAdapter(SQL, strcon);
            sqlda.Fill(ds, "ten bang");
            dg.DataSource = ds.Tables[0];
        }

        public string LoadLenLable(string sql)
        {
            string KetQua = "";
            MoKeNoi();
            sqlcom = new SqlCommand(sql, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                KetQua = sqldr[0].ToString();
            }
            DongKetNoi();
            return KetQua;
        }
                
        public int LayGiaTri(string sql, string dulieuvao)
        {
            MoKeNoi();
            string a = "";
            sqlcom = new SqlCommand(sql, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                if (dulieuvao == sqldr[0].ToString())
                {
                    a = sqldr[1].ToString();
                }
            }
            int n = int.Parse(a);
            return n;
        }

        public bool KiemTra(string sql)
        {
            MoKeNoi();
            sqlcom = new SqlCommand(sql, sqlcon);
            int n = (int)sqlcom.ExecuteScalar();
            DongKetNoi();
            if (n > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool KiemTraUser(string SQL, string User, string Pass)
        {
            MoKeNoi();
            bool a = true;
            sqlcom = new SqlCommand(SQL, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                if (User == sqldr[0].ToString() && Pass == sqldr[1].ToString())
                {
                    a = false;
                }
                else
                {
                    a = true;
                }
            }
            return a;
        }

        public bool KiemTraUserName(string strSQL)
        {
            MoKeNoi();
            //
            sqlcom = new SqlCommand(strSQL, sqlcon);
            int TonTai = (int)(sqlcom.ExecuteScalar());
            //
            DongKetNoi();
            //
            if (TonTai > 0) { return false; }
            else { return true; }
        }

        //public void AttachDatabase(string strServerName, string strDatabaseName)
        //{
        //    bool check = false;
        //    Server sv = new Server(strServerName);
        //  //  //Lấy danh sách các CSDL và đưa vào Listbox
        //    foreach (Database db in sv.Databases)
        //    {
        //        if (db.Name== strDatabaseName)
        //        {
        //            check = true;
        //        }
        //    }
        //   //// Kiểm tra biến check:
        //   // //Nếu bằng False: Chưa có trong CSDL thì Attach vào và ngược lại
        //    if (check == false)
        //    {
        //       ////Trủ tới 2 file .mdf và ldf trong thư mục Debug(Có thể dùng Environment hoặc Application)
        //        string strPathData = Environment.CurrentDirectory + @"\" + strDatabaseName + ".mdf";
        //        string strPathLog = Environment.CurrentDirectory + @"\" + strDatabaseName + "_Log.ldf";

        //       // //Chuỗi kết nối
        //        string strCon = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Master; Data Source=.\SQLEXPRESS";
        //        string strAttach = "EXEC sp_attach_db 'SampleDBProgramming', '" + strPathData + "','" + strPathLog + "' ";
        //        string strCon = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Master; Data Source='" + strServerName + "' ";
        //        string strAttach = "EXEC sp_attach_db '" + strDatabaseName + "', '" + strPathData + "','" + strPathLog + "' ";

        //       //// Thực thi như một câu lệnh SQL
        //        sqlcon = new SqlConnection(strCon);
        //        sqlcon.Open();
        //        sqlcom = new SqlCommand(strAttach, sqlcon);
        //        sqlcom.ExecuteNonQuery();
        //        sqlcon.Close();
        //    }
        //    else
        //    {
        //        MessageBox.Show("Cơ sở dữ liệu: " + strDatabaseName + " đã tồn tại", "Attach");
        //    }
        //}

        //public void InAn(string SQL,CrystalReport4 cr)
        //{
        //    ds = new DataSet();
        //    sqlda = new SqlDataAdapter(SQL, strcon);
        //    sqlda.Fill(ds, "ten bang");
        //    cr.SetDataSource(ds);
        //}

       
    }
}